﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Data;
using Data;
using DataAccess;

namespace Business
{
	public class Comment
	{
		CreateConn obj;
		DataTable dt;
		public int component_id { get; set; }
		public int status_id { get; set; }
		public string comments { get; set; }
		public DateTime dateEntered { get; set; }
		public DateTime dateRecorded { get; set; }
		public int recommendId_1 { get; set; }
		public int recommendId_2 { get; set; }
		public int recommendId_3 { get; set; }
		public int resurvey_Id { get; set; }
		int reco1 = 0, reco2 = 0, reco3 = 0;
		public static int[] recos = new int[3];
        i2iPortalLinqToSQLDataContext dataContext = new i2iPortalLinqToSQLDataContext();

		public Comment()
		{
			obj = new CreateConn();
		}
		public DataTable getTemplate()
		{
			try
			{
				string query = "SELECT * FROM StandardComments";
				dt = obj.SQLConnDataTable(query);
			}
			catch (Exception e)
			{
				String Errmsg = e.ToString();
			}
			return dt;
		}
		public DataTable getFaults(int componentID)
		{
			string query = "SELECT * FROM FaultsCondition WHERE faultType=0 and fault_id IN (SELECT fault_id" +
				" FROM Category_Fault WHERE category_id IN(" +
				"SELECT category_id FROM Components WHERE " +
				"component_id='" + componentID + "') OR " +
				"category_id=1)";
			try
			{
				dt = obj.SQLConnDataTable(query);
			}
			catch (Exception e)
			{
				String Errmsg = e.ToString();
			}
			return dt;
		}

		public void copyOldReport(int componentID, string dateOfSurvey)
		{
			var previousComponentStatus = GetPreviousRecomm(componentID, dateOfSurvey);
		    if (previousComponentStatus != null)
		    {
                   insertNewreport(componentID, previousComponentStatus);
			}
		}

	    public void insertNewreport(int componentId, ComponentStatus cs)
	    {
	        int statusID = 0;
	        if (cs.status_id != null)
	        {
	            statusID = cs.status_id.Value;
	        }

	        int rec1 = cs.recommendID_1 ?? 0;
	        int rec2 = cs.recommendID_2 ?? 0;
	        int rec3 = cs.recommendID_3 ?? 0;
	        int resurveyID = cs.resurvey_id ?? 0;
            insertNewreport(componentId, statusID, cs.comments, cs.dateRecorded.ToString("dd/MM/yyyy"), rec1, rec2,
                rec3, resurveyID);
	    }

	    public bool insertNewreport(int componentID, int status_id, string comments, string dateRecorded, int reco1, int reco2, int reco3, int resury_id)
		{
			string query = "";
			var dateInfo = new DateTimeFormatInfo();
			dateInfo.ShortDatePattern = "dd/MM/yyyy";
			DateTime dt2 = Convert.ToDateTime(dateRecorded, dateInfo);
			string delete_query = "DELETE FROM ComponentStatus"
			  + " WHERE component_id='" + componentID + "' and dateRecorded=convert(datetime,'" + dt2.Date + "',103)";
			CreateConn obj_conn = new CreateConn();
			try
			{
				obj_conn.SQLConnDataTable(delete_query);
			}
			catch (Exception e)
			{
			}
			if (resury_id == 0)
			{
				if (reco2 == 0)
				{
					query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1) values('" + componentID
					  + "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1
					  + "')";
				}
				else if (reco3 == 0)
				{
					query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1,recommendID_2) values('" + componentID
					+ "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1 + "','" + reco2 + "')";
				}
				else
				{
					query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1,recommendID_2,recommendID_3) values('" + componentID
					+ "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1 + "','" + reco2 + "','" + reco3 + "')";
				}
			}
			else
			{
				if (reco2 == 0)
				{
					query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1,resurvey_id) values('" + componentID
					  + "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1
					  + "','" + resury_id + "')";
				}
				else if (reco3 == 0)
				{
					query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1,recommendID_2,Resurvey_id) values('" + componentID
					+ "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1 + "','" + reco2
					+ "','" + resury_id + "')";
				}
				else
				{
					query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1,recommendID_2,recommendID_3,Resurvey_id) values('" + componentID
					+ "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1 + "','" + reco2 + "','" + reco3
					+ "','" + resury_id + "')";
				}
			}
			try
			{
				dt = obj.SQLConnDataTable(query);

			}
			catch (Exception e)
			{
			}
			return true;
		}

		public Comment GetComments(String comments,int componentId)
		{
			String query = "SELECT * FROM ComponentStatus cs WHERE cs.comments = '"+comments+"'and component_id = '"+componentId+"'";
			DataTable dtComment = obj.SQLConnDataTable(query);
			Comment returnValue = new Comment();
			foreach (DataRow dr in dtComment.Rows)
			{
				returnValue.component_id = componentId;
				returnValue.status_id = Int32.Parse(dr["status_id"].ToString()) ;
				returnValue.comments = comments;
				returnValue.recommendId_1 = Int32.Parse(dr["recommendID_1"].ToString());
				if (dr["recommendID_2"] != null && !dr["recommendID_2"].ToString().Equals(string.Empty)) returnValue.recommendId_2 = Int32.Parse(dr["recommendID_2"].ToString());
				if (dr["recommendID_3"] != null && !dr["recommendID_3"].ToString().Equals(string.Empty)) returnValue.recommendId_3 = Int32.Parse(dr["recommendID_3"].ToString());
				if (dr["resurvey_id"] != null && !dr["resurvey_id"].ToString().Equals(string.Empty)) returnValue.recommendId_2 = Int32.Parse(dr["resurvey_id"].ToString());
				return returnValue;
			}
			return null;
		}

		public ComponentStatus GetPreviousRecomm(int component_id, string surveydate)
		{
		    var dateSurveyed = DateTime.ParseExact(surveydate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
		    var query1 = from c in dataContext.ComponentStatus
		        where c.component_id == component_id && c.dateRecorded.Date <= dateSurveyed.Date.AddMilliseconds(1)
		        orderby c.dateRecorded descending 
                         select c;

		    var previousComponentStatus = query1.FirstOrDefault();
		    return previousComponentStatus;
		}

		public DataTable GetComments(int component_id)
		{
			string query = "select DISTINCT(comments) AS comments from ComponentStatus WHERE " +
				"component_id='" + component_id +"'";
			try
			{
				dt = obj.SQLConnDataTable(query);
			}
			catch (Exception e)
			{
				String Errmsg = e.ToString();
			}
			return dt;
		}



		public DataTable getRecomm2()
		{
			string query = "SELECT * FROM Recommendation";
			try
			{

				dt = obj.SQLConnDataTable(query);
			}
			catch (Exception e)
			{
			}
			return dt;
		}
		public DataTable getCurrentRecom(int component_id, string date)
		{

			string query = "select status_id,comments,CONVERT(varchar(10), dateRecorded, 103) as dateRecorded1,recommendID_1,recommendID_2,recommendID_3,resurvey_id from ComponentStatus WHERE " +
				"dateRecorded = convert(datetime,'" + date + "',103) and component_id='" + component_id + "'";
			try
			{
				dt = obj.SQLConnDataTable(query);
			}
			catch (Exception e)
			{
				String Errmsg = e.ToString();
			}
			return dt;
		}
		public DataTable getRecomm(int Fault1ID, int statusID, int Fault2ID, int status2ID, int Fault3ID, int status3ID)//Get Recommendation using given fault and severity
		{

			DataTable dt2 = null;
			DataTable result_table = new DataTable();

			string query = "SELECT * FROM FaultRecommendation WHERE Fault_id = '" + Fault1ID + "'AND Status_id ='" + statusID + "'";
			try
			{

				dt = obj.SQLConnDataTable(query);
				result_table = dt.Copy();
				result_table.Rows.Clear();
			}
			catch (Exception e)
			{
			}
			if (Fault2ID != 0 && Fault3ID == 0)
			{
				string query2 = "SELECT * FROM FaultRecommendation WHERE Fault_id = '" + Fault2ID + "'AND Status_id ='" + status2ID + "'";
				dt2 = obj.SQLConnDataTable(query2);
				result_table = obj.SQLConnDataTable(query2);
				result_table.Rows.Clear();
				if (dt2.Rows.Count > 0)
				{

					foreach (DataRow dr in dt.Rows)
					{
						string comment1 = dr["recommendation"].ToString();
						foreach (DataRow dr2 in dt2.Rows)
						{
							string comment2 = dr2["recommendation"].ToString();

							if (comment1.Equals(comment2))
							{
								DataRow dr_res = result_table.NewRow();
								dr_res["recommend_id"] = Int32.Parse(dr2["recommend_id"].ToString());
								dr_res["recommendation"] = comment2;
								result_table.Rows.Add(dr_res);
								reco1 = Int32.Parse(dr["recommend_id"].ToString());
								reco2 = Int32.Parse(dr2["recommend_id"].ToString());

								string query4 = "SELECT * FROM FaultRecommendation WHERE recommend_id='" + reco1 + "'";
								dt = obj.SQLConnDataTable(query4);
							}
						}
					}
				}
				return result_table;

			}
			else if (Fault2ID != 0 && Fault3ID != 0)
			{
				string query2 = "SELECT * FROM FaultRecommendation WHERE Fault_id = '" + Fault2ID + "'AND Status_id ='" + status2ID + "'";
				dt2 = obj.SQLConnDataTable(query2);
				string query3 = "SELECT * FROM FaultRecommendation WHERE Fault_id = '" + Fault3ID + "'AND Status_id ='" + status3ID + "'";
				DataTable dt3 = obj.SQLConnDataTable(query3);
				foreach (DataRow dr in dt.Rows)
				{
					string comment1 = dr["recommendation"].ToString();
					if (dt2.Rows.Count > 0 && dt3.Rows.Count > 0)
					{
						foreach (DataRow dr2 in dt2.Rows)
						{
							string comment2 = dr2["recommendation"].ToString();
							foreach (DataRow dr3 in dt3.Rows)
							{
								string comment3 = dr3["recommendation"].ToString();
								if (comment1.Equals(comment2) && comment1.Equals(comment3))
								{
									DataRow dr_res = result_table.NewRow();
									dr_res["recommend_id"] = Int32.Parse(dr2["recommend_id"].ToString());
									dr_res["recommendation"] = comment2;
									result_table.Rows.Add(dr_res);
									reco1 = Int32.Parse(dr["recommend_id"].ToString());
									reco2 = Int32.Parse(dr2["recommend_id"].ToString());
									reco3 = Int32.Parse(dr3["recommend_id"].ToString());
								}
							}
						}
					}
				}
				recos[0] = reco1;
				recos[1] = reco2;
				recos[2] = reco3;
				return result_table;
			}
			else
			{
				return dt;
			}
		}
		public void insertTemplate(string template, int status_id)
		{
			try
			{
				string query = "INSERT INTO StandardComments (commentDescription,status_id) VALUES('" + template + "','" + status_id + "')";
				dt = obj.SQLConnDataTable(query);
			}
			catch (Exception e)
			{
				String Errmsg = e.ToString();
			}

		}
		public DataTable getResurvey()
		{
			try
			{
				string query = "SELECT * FROM Resurvey";
				dt = obj.SQLConnDataTable(query);
			}
			catch (Exception e)
			{
			}
			return dt;
		}
		public void InsertMatrix(string daterecorded, int machine_id, int matrix_no)
		{
			try
			{
				string query = "INSERT INTO Table MachineMatrix(date_recorded,machine_id,matrix_no) values" +
					"(convert(datetime,'" + daterecorded + "', 103),'" + machine_id + "','" + matrix_no + "')";
				dt = obj.SQLConnDataTable(query);
			}
			catch (Exception e)
			{
				String Errmsg = e.ToString();
			}

		}

		public DataTable GetCommentsByStatusId(int componentId, int statusSelected)
		{
			string query ;
			query = "select DISTINCT(comments) AS comments from ComponentStatus WHERE " +
"component_id='" + componentId + "' and status_id = '"+statusSelected + "'";
			try
			{
				dt = obj.SQLConnDataTable(query);
			}
			catch (Exception e)
			{
				String Errmsg = e.ToString();
			}
			return dt;
		}

		public int GetRecomId(int faultId, int statusId)
		{
		    var query = from fr in dataContext.FaultRecommendation
		        where fr.fault_id == faultId && fr.status_id == statusId
		        select fr;

		    var rec = query.FirstOrDefault();
		    if (rec != null)
		    {
				return rec.recommend_id;
		    }
		    else
		    {
		        var recToInsert = new FaultRecommendation()
		        {
                    fault_id = faultId,
                    status_id = statusId,
		        };
                dataContext.FaultRecommendation.InsertOnSubmit(recToInsert);
                dataContext.SubmitChanges();
		        return recToInsert.recommend_id;
		    }
		}

	    public void RemoveCurrentComment(int componentId, string surveydate)
	    {
	        var dataContext = new i2iPortalLinqToSQLDataContext();
            DateTime surveyDate = DateTime.ParseExact(surveydate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
	        var previousReport = dataContext.ComponentStatus.Where(c => c.component_id == componentId && c.dateRecorded == surveyDate).ToList();
            dataContext.ComponentStatus.DeleteAllOnSubmit(previousReport);
	        dataContext.SubmitChanges();
	    }
	}
}

